package com.iretailer.report.influxdb.dao;

import cn.hutool.core.date.DatePattern;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.iretailer.report.influxdb.model.*;
import com.iretailer.report.influxdb.util.TableName;
import com.iretailer.report.model.AlarmEasModel;
import com.iretailer.report.model.ZoneCoefficient;
import com.iretailer.report.service.CameraZoneService;
import com.iretailer.report.service.CoefficientService;
import com.iretailer.report.service.CoefficientZoneService;
import com.iretailer.report.util.CommonUtil;
import com.iretailer.report.util.TimeUtil;
import org.influxdb.InfluxDB;
import org.influxdb.dto.Point;
import org.influxdb.dto.Query;
import org.influxdb.dto.QueryResult;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Repository;

import java.time.LocalDate;
import java.time.ZonedDateTime;
import java.time.format.DateTimeFormatter;
import java.util.*;
import java.util.concurrent.TimeUnit;
import java.util.stream.Collectors;

/**
 * Created by xjw on 2018/3/30.
 */
@Repository
public class InfluxdbDao {

    private Logger log = LoggerFactory.getLogger(InfluxdbDao.class);
    @Value("${influxdb.dbName}")
    private String dbName;
    @Value("${influxdb.url}")
    private String url;
    @Autowired
    private CoefficientService coefficientService;
    @Autowired
    private CameraZoneService cameraZoneService;
    @Autowired
    private CoefficientZoneService coefficientZoneService;
    @Autowired
    private InfluxdbBaseDao influxdbBaseDao;

    public int batchInsertEasAlarm(List<EasAlarm> post, TableName.EasAlarm table) {
        if (!post.isEmpty()) {
            List<Point> insertList = new ArrayList<>();
            for (EasAlarm alarm : post) {
                insertList.add(Point.measurement(table.value())
                        .time(TimeUtil.getLong(alarm.getTime()), TimeUnit.MILLISECONDS)
                        .tag("zoneid", alarm.getZoneid())
                        .addField("alarmtimes", alarm.getAlarmTimes())
                        .build());
            }
            influxdbBaseDao.insert(url, dbName, insertList);
            return post.size();
        }
        return -1;
    }

    /***
     * 插入 passby 数据
     * @param post
     * @param table
     * @return
     */
    public int batchInsertPassbyFlow(List<PassByFlow> post, TableName.PassByflow table) {
        return batchInsertPassbyFlow(post, table, true);
    }

    /**
     * @param calculateFactor 是否计算系数
     */
    public int batchInsertPassbyFlow(List<PassByFlow> post, TableName.PassByflow table, boolean calculateFactor) {
        if (!post.isEmpty()) {
            List<PassByFlow> data;
            if (calculateFactor && table.equals(TableName.PassByflow.Passbyflow)) {
                data = new ArrayList<>();
                Map<String, List<PassByFlow>> dataMap = post.stream().collect(Collectors.groupingBy(PassByFlow::getZoneid));
                Map<String, List<ZoneCoefficient>> coefficientMap = new HashMap<>();
                for (String key : dataMap.keySet()) {
                    List<ZoneCoefficient> coefficients = coefficientMap.get(key);
                    if (coefficients == null) {
                        coefficients = coefficientZoneService.selectMallCoefficient(new QueryWrapper<>().eq("ZoneId", key));
                        if (!coefficients.isEmpty()) {
                            coefficientMap.put(key, coefficients);
                        }
                    }
                    if (coefficients.isEmpty()) {
                        data.addAll(dataMap.get(key));
                    } else {
                        for (PassByFlow p : dataMap.get(key)) {
                            boolean flag = false;
                            for (ZoneCoefficient coefficient : coefficients) {
                                if (inThisDuration(p.getTime(), coefficient)) {
                                    p.setCountPass((int) Math.floor(p.getCountPass() * coefficient.getValue()));
                                    p.setCountEnter((int) Math.floor(p.getCountEnter() * coefficient.getValue()));
                                    data.add(p);
                                    flag = true;
                                    break;
                                }
                            }
                            if (!flag) {
                                data.add(p);
                            }
                        }
                    }
                }
            } else {
                data = post;
            }
            List<Point> insertList = new ArrayList<>();
            for (PassByFlow pb : data) {
                insertList.add(Point.measurement(table.value())
                        .time(TimeUtil.getLong(pb.getTime()), TimeUnit.MILLISECONDS)
                        .tag("zoneid", pb.getZoneid())
                        .addField("countpass", pb.getCountPass())
                        .addField("countenter", pb.getCountEnter()).build());
            }
            influxdbBaseDao.insert(url, dbName, insertList);
            return post.size();
        }
        return -1;
    }

    /**
     * 向 queuelength 插入数据
     *
     * @param post
     * @return
     */
    public int batchInsertQueueLength(List<QueueLength> post, TableName.Queue table) {
        if (!post.isEmpty()) {
            List<Point> insertList = new ArrayList<>();
            for (QueueLength qu : post) {
                insertList.add(Point.measurement(table.value())
                        .time(TimeUtil.getLong(qu.getTime()), TimeUnit.MILLISECONDS)
                        .tag("zoneid", qu.getZoneId())
                        .addField("avglength", qu.getAvgLength())
                        .addField("maxlength", qu.getMaxLength()).build());
            }
            influxdbBaseDao.insert(url, dbName, insertList);
            return post.size();
        }
        return -1;
    }

    /**
     * 向 Wifi 表中批量插入数据
     *
     * @param post
     * @return
     */
    public int batchInsertWifi(List<Wifi> post, TableName.Wifi table) {
        int result = -1;
        if (!post.isEmpty()) {
            List<Point> insertList = new ArrayList<>();
            for (Wifi w : post) {
                insertList.add(Point.measurement(table.value())
                        .time(TimeUtil.getLong(w.getTime()), TimeUnit.MILLISECONDS)
                        .tag("zoneid", w.getZoneId())
                        .addField("ipaddr", w.getIpaddr())
                        .addField("macdetected", w.getMacDetected())
                        .addField("enter", w.getEnter())
                        .addField("rssi", w.getRssi())
                        .addField("type", w.getType())
                        .addField("macorganization", w.getMacorganization()).build());
            }
            influxdbBaseDao.insert(url, dbName, insertList);
            result = post.size();
        }
        return result;
    }


    /**
     * 向 dwelltiem 中批量插入
     *
     * @param post
     * @return
     */
    public int batchInsertDwelltime(List<DwellTime> post, TableName.Dwell table) {
        int result = -1;
        if (!post.isEmpty()) {
            List<Point> insertList = new ArrayList<>();
            for (DwellTime d : post) {
                insertList.add(Point.measurement(table.value())
                        .time(TimeUtil.getLong(d.getTime()), TimeUnit.MILLISECONDS)
                        .tag("zoneid", d.getZoneid())
                        .addField("numberserved", d.getNumberServed())
                        .addField("secondsOccupied", d.getSecondsOccupied()).build());
            }
            influxdbBaseDao.insert(url, dbName, insertList);
            result = post.size();
        }
        return result;
    }


    /**
     * 向 dwelltime_physicalzone 中批量插入
     *
     * @param post
     * @return
     */
    public int batchInsertDwelltimePZ(List<DwellTimePZ> post) {
        int result = -1;
        if (!post.isEmpty()) {

            List<Point> insertList = new ArrayList<>();
            for (DwellTimePZ d : post) {
                insertList.add(Point.measurement(TableName.Dwell.DwellTime_PZ.value())
                        .time(TimeUtil.getLong(d.getTime()), TimeUnit.MILLISECONDS)
                        .tag("zoneid", d.getZoneid())
                        .addField("entrancecount", d.getEntranceCount())
                        .addField("passcount", d.getPassCount())
                        .addField("staycount", d.getStayCount())
                        .addField("secondscount", d.getSecondsCount()).build());
            }
            influxdbBaseDao.insert(url, dbName, insertList);
            result = post.size();
        }
        return result;
    }


    /**
     * 向 passengerflow 批量插入
     *
     * @param post
     * @return
     */
    public int batchInsertPassengerflow(List<Passengerflow> post, TableName.Passengerflow table) {

        int result = -1;
        if (!post.isEmpty()) {
            List<Point> insertList = new ArrayList<>();
            for (Passengerflow p : post) {
                insertList.add(Point.measurement(table.value())
                        .time(TimeUtil.getLong(p.getTime()), TimeUnit.MILLISECONDS)
                        .tag("zoneid", p.getZoneid())
                        .addField("countenter", p.getCountEnter())
                        .addField("countexit", p.getCountExit()).build());
            }
            influxdbBaseDao.insert(url, dbName, insertList);
            result = post.size();
        }
        return result;
    }

    private boolean inThisDuration(ZonedDateTime time, ZoneCoefficient coefficient) {
        LocalDate startDate = coefficient.getStartDate();
        LocalDate endDate = coefficient.getEndDate();
        LocalDate localDate = time.toLocalDateTime().toLocalDate();
        String week = localDate.getDayOfWeek().name();
        if (localDate.equals(startDate) || localDate.equals(endDate) || localDate.isBefore(endDate) || localDate.isAfter(endDate)) {
            String weekdays = coefficient.getWeekdays();
            for (String wd : weekdays.split(",")) {
                if (week.startsWith(wd.toUpperCase())) {
                    return true;
                }
            }
        }
        return false;
    }


    /**
     * 查询 dwelltime 相关的表
     *
     * @param table
     * @param zoneIds
     * @param startTime
     * @param endTime
     * @return
     */
    public List<DwellTime> selectDwelltime(String timeZone, String table, List<Integer> zoneIds, String startTime, String endTime) {
        List<DwellTime> result = null;
        InfluxDB influxDB = InfluxdbBaseDao.MyInfluxDBUtil.getInfluxDB(url);
        String sqlf = "SELECT * FROM %s where zoneid = '%d' and time>='%s' and time<'%s' order by time tz('%s');";
        StringBuffer sqlBf = new StringBuffer();
        if (!zoneIds.isEmpty()) {
            for (Integer zoneId : zoneIds) {
                sqlBf.append(String.format(sqlf, table, zoneId, startTime, endTime, timeZone));
            }
            QueryResult queryResult = influxDB.query(new Query(sqlBf.toString(), dbName, true));

            return queryResult2T(queryResult, DwellTime.class);

        } else {
            return Collections.emptyList();
        }
    }

    public List<AlarmEasModel> selectAlarmEas(String timeZone, String startTime, String endTime, List<String> deviceIds) {
        String sqlTemplate = "SELECT * from eas_alarm WHERE time>='%s' and time<='%s' %s fill(0) order by time tz('%s');";
        List<Object> objects = new ArrayList<>();
        objects.add(startTime);
        objects.add(endTime);
        if (deviceIds != null && !deviceIds.isEmpty()) {
            String deviceIdCondition = deviceIds.stream().map(deviceId -> String.format(" device_id = '%s' ", deviceId)).collect(Collectors.joining(" or "));
            objects.add(String.format(" and (%s) ", deviceIdCondition));
        }else{
            objects.add("");
        }
        objects.add(timeZone);
        String sql = String.format(sqlTemplate, objects.toArray(new Object[0]));
        InfluxDB influxDB = InfluxdbBaseDao.MyInfluxDBUtil.getInfluxDB(url);
        QueryResult queryResult = influxDB.query(new Query(sql, dbName, true));
        List<QueryResult.Result> results = queryResult.getResults();
        List<AlarmEasModel> models = new ArrayList<>();
        if (results != null && !results.isEmpty()) {
            for (QueryResult.Result result : results) {
                List<QueryResult.Series> seriesList = result.getSeries();
                if (seriesList != null && !seriesList.isEmpty()) {
                    for (QueryResult.Series series : seriesList) {
                        List<String> columns = series.getColumns();
                        List<List<Object>> values = series.getValues();
                        int timeIndex = CommonUtil.findIndex(columns, "time");
                        int deviceIdIndex = CommonUtil.findIndex(columns, "device_id");
                        int epcIndex = CommonUtil.findIndex(columns, "epc");
                        int rssiIndex = CommonUtil.findIndex(columns, "rssi");
                        int ruleNumIndex = CommonUtil.findIndex(columns, "rule_num");
                        for (List<Object> value : values) {
                            AlarmEasModel model = new AlarmEasModel();
                            String t = new StringBuffer(new StringBuffer(value.get(timeIndex).toString()).reverse().toString().replaceFirst(":", "")).reverse().toString();
                            model.setTime(ZonedDateTime.parse(t, DateTimeFormatter.ofPattern(DatePattern.UTC_WITH_ZONE_OFFSET_PATTERN)));
                            model.setDeviceId(Integer.valueOf(value.get(deviceIdIndex).toString()));
                            model.setEpc(value.get(epcIndex).toString());
                            model.setRssi(((Double)value.get(rssiIndex)).intValue());
                            model.setRuleNum(((Double)value.get(ruleNumIndex)).intValue());
                            models.add(model);
                        }
                    }
                }
            }
        }
        return models;
    }

    public List<AlarmEasModel> selectSummaryAlarmEas(String timeZone, List<Integer> deviceIds, String startTime, String endTime, String timePeriod) {
        InfluxDB influxDB = InfluxdbBaseDao.MyInfluxDBUtil.getInfluxDB(url);
        if (!deviceIds.isEmpty()) {
            String sql = deviceIds.stream().map(deviceId -> String.format(
                    "SELECT sum(alarm_times) FROM eas_alarm_summary WHERE device_id = '%s' and time>='%s' and time<='%s' group by device_id,time(%s) fill(0) order by time tz('%s');",
                    deviceId, startTime, endTime, timePeriod, timeZone)).collect(Collectors.joining(""));
            log.info("selectAlarmEas:\n{}", sql);
            QueryResult queryResult = influxDB.query(new Query(sql, dbName, true));
            List<QueryResult.Result> results = queryResult.getResults();
            if (results != null && !results.isEmpty()) {
                List<AlarmEasModel> models = new ArrayList<>();
                for (QueryResult.Result result : results) {
                    List<QueryResult.Series> seriesList = result.getSeries();
                    if (seriesList != null && !seriesList.isEmpty()) {
                        for (QueryResult.Series series : seriesList) {
                            Map<String, String> tags = series.getTags();
                            List<List<Object>> values = series.getValues();
                            for (List<Object> value : values) {
                                AlarmEasModel model = new AlarmEasModel();
                                String time = (String) value.get(0);
                                Integer times = ((Double) value.get(1)).intValue();
                                model.setAlertTimes(times);
                                model.setDeviceId(Integer.valueOf(tags.get("device_id")));
                                String t = new StringBuffer(new StringBuffer(time).reverse().toString().replaceFirst(":", "")).reverse().toString();
                                model.setTime(ZonedDateTime.parse(t, DateTimeFormatter.ofPattern(DatePattern.UTC_WITH_ZONE_OFFSET_PATTERN)));
                                models.add(model);
                            }
                        }
                    }
                }
                return models;
            }
        }
        return Collections.emptyList();

    }

    /**
     * 查询出 table 表的数据
     *
     * @param table
     * @param startTime
     * @param endTime
     * @return
     */
    public List<Passengerflow> selectflow(String timeZone, String table, List<Integer> zoneIds, String startTime, String endTime) {
        List<Passengerflow> result = null;
        InfluxDB influxDB = InfluxdbBaseDao.MyInfluxDBUtil.getInfluxDB(url);
        String sqlf = "SELECT * FROM %s where zoneid = '%d' and time>='%s' and time<'%s' order by time tz('%s');";
        StringBuffer sqlBf = new StringBuffer();
        if (!zoneIds.isEmpty()) {
            for (Integer zoneId : zoneIds) {
                sqlBf.append(String.format(sqlf, table, zoneId, startTime, endTime, timeZone));
            }
            QueryResult queryResult = influxDB.query(new Query(sqlBf.toString(), dbName, true));

            return queryResult2T(queryResult, Passengerflow.class);

        } else {
            return Collections.emptyList();
        }
    }

    /**
     * 查询 queue 队列长度
     *
     * @param table
     * @param zoneIds
     * @param startTime
     * @param endTime
     * @return
     */

    public List<QueueLength> selectQueueLength(String timeZone, String table, List<Integer> zoneIds, String startTime, String endTime) {
        InfluxDB influxDB = InfluxdbBaseDao.MyInfluxDBUtil.getInfluxDB(url);
        String sqlf = "SELECT * FROM %s where zoneid = '%d' and time>='%s' and time<'%s' order by time tz('%s');";
        StringBuilder sqlBi = new StringBuilder();
        if (zoneIds.size() > 0) {
            for (Integer zoneId : zoneIds) {
                sqlBi.append(String.format(sqlf, table, zoneId, startTime, endTime, timeZone));
            }
            QueryResult queryResult = influxDB.query(new Query(sqlBi.toString(), dbName, true));
            return queryResult2T(queryResult, QueueLength.class);
        } else {
            return Collections.emptyList();
        }
    }

    public List<DwellTime> selectDwellGroupByZoneId(boolean isFill, String timeZone, TableName.Dwell table, List<Long> zoneIds, String startTime, String endTime, String groupBy) {
        String zoneIdOr = zoneIdOrStr(zoneIds);
        if (zoneIdOr != null) {
            InfluxDB influxDB = InfluxdbBaseDao.MyInfluxDBUtil.getInfluxDB(url);
            StringBuilder sqlf = new StringBuilder("SELECT SUM(numberserved) as numberserved,SUM(secondsOccupied) as secondsOccupied FROM %s where %s and time>='%s' and time<'%s' group by zoneid %s ");
            if (isFill) {
                sqlf.append(" fill(0) ");
            }
            sqlf.append(" order by time tz('%s')");
            if (groupBy.equals("All")) {
                groupBy = "";
            } else {
                groupBy = String.format(", time(%s)", groupBy);
            }
            String sql = String.format(sqlf.toString(), table.value(), zoneIdOr, startTime, endTime, groupBy, timeZone);
            log.debug("------ [{}] ", sql);
            QueryResult queryResult = influxDB.query(new Query(sql, dbName, true));
            return queryResult2T(queryResult, DwellTime.class);
        } else {
            return Collections.emptyList();
        }
    }

    public List<DwellTimePZ> selectDwellTimePZGroupByZoneId(String timeZone, TableName.Dwell table, List<Long> zoneIds, String startTime, String endTime, String groupBy) {
        String zoneIdOr = zoneIdOrStr(zoneIds);
        if (zoneIdOr != null) {
            InfluxDB influxDB = InfluxdbBaseDao.MyInfluxDBUtil.getInfluxDB(url);
            String sqlf = "SELECT SUM(entrancecount) as entrancecount, SUM(passcount) as passcount,SUM(staycount) as staycount,SUM(secondscount) as secondscount FROM %s where %s and time>='%s' and time<'%s' group by zoneid %s fill(0) order by time  tz('%s')";
            if (groupBy.equals("All")) {
                groupBy = "";
            } else {
                groupBy = String.format(", time(%s)", groupBy);
            }
            String sql = String.format(sqlf, table.value(), zoneIdOr, startTime, endTime, groupBy, timeZone);
            log.debug("------ [{}] ", sql);
            QueryResult queryResult = influxDB.query(new Query(sql, dbName, true));
            return queryResult2T(queryResult, DwellTimePZ.class);
        } else {
            return Collections.emptyList();
        }
    }

    /**
     * @Author xjw
     * @Date 2019/3/12 11:31
     * @Description : 用于汇总到 区域
     **/
    public List<DwellTime> selectDwellGroupByTime(String timeZone, TableName.Dwell table, List<Long> zoneIds, String startTime, String endTime, String period) {
        String zoneIdOr = zoneIdOrStr(zoneIds);
        if (zoneIdOr != null) {
            InfluxDB influxDB = InfluxdbBaseDao.MyInfluxDBUtil.getInfluxDB(url);
            String sqlf = "SELECT SUM(numberserved) as numberserved,SUM(secondsOccupied) as secondsOccupied FROM %s where %s and time>='%s' and time<'%s' group by time(%s) order by time  tz('%s')";
            String sql = String.format(sqlf, table.value(), zoneIdOr, startTime, endTime, period, timeZone);
            QueryResult queryResult = influxDB.query(new Query(sql, dbName, true));
            return queryResult2T(queryResult, DwellTime.class);
        } else {
            return Collections.emptyList();
        }
    }

    /**
     * dwelltime 根据 zoneid  group by
     *
     * @param table
     * @param zoneIds
     * @param startTime
     * @param endTime
     * @return
     */
    public List<DwellTime> selectStayflowGroupByOnlyZoneid(String timeZone, String table, List<Long> zoneIds, String startTime, String endTime) {

        String zoneIdOr = zoneIdOrStr(zoneIds);
        if (zoneIdOr != null) {
            InfluxDB influxDB = InfluxdbBaseDao.MyInfluxDBUtil.getInfluxDB(url);
            String sqlf = "SELECT SUM(numberserved) as StayCount,SUM(secondsOccupied) as secondsCount,SUM(secondsOccupied)/SUM(numberserved) as StayDuration FROM %s where %s and time>='%s' and time<'%s' group by zoneid fill(0) order by time  tz('%s')";
            String sql = String.format(sqlf, table, zoneIdOr, startTime, endTime, timeZone);
            log.debug("------ [{}] ", sql);
            QueryResult queryResult = influxDB.query(new Query(sql, dbName, true));

            return queryResult2T(queryResult, DwellTime.class);
        } else {
            return Collections.emptyList();
        }
    }

    /**
     * dwelltime 根据 zoneid time  group by
     *
     * @param table
     * @param zoneIds
     * @param startTime
     * @param endTime
     * @param groupBy
     * @return
     */
    public List<DwellTime> selectStayflowGroupByZoneid(String timeZone, String table, List<Long> zoneIds, String startTime, String endTime, String groupBy) {

        String zoneIdOr = zoneIdOrStr(zoneIds);
        if (zoneIdOr != null) {

            InfluxDB influxDB = InfluxdbBaseDao.MyInfluxDBUtil.getInfluxDB(url);
            String sqlf = "SELECT SUM(numberserved) as StayCount,SUM(secondsOccupied) as secondsCount,SUM(secondsOccupied)/SUM(numberserved) as StayDuration FROM %s where %s and time>='%s' and time<'%s' group by zoneid,time(%s) fill(0) order by time  tz('%s')";
            String sql = String.format(sqlf, table, zoneIdOr, startTime, endTime, groupBy, timeZone);
            log.debug("------ [{}] ", sql);
            QueryResult queryResult = influxDB.query(new Query(sql, dbName, true));
            return queryResult2T(queryResult, DwellTime.class);
        } else {
            return Collections.emptyList();
        }
    }

    /**
     * queue 根据 zoneid time  group by
     *
     * @param table
     * @param zoneIds
     * @param startTime
     * @param endTime
     * @param groupBy
     * @return
     */
    public List<QueueLength> selectQueueGroupByZoneIds(String timeZone, String table, List<Long> zoneIds, String startTime, String endTime, String groupBy) {
        String zoneIdOr = zoneIdOrStr(zoneIds);
        if (zoneIdOr != null) {
            InfluxDB influxDB = InfluxdbBaseDao.MyInfluxDBUtil.getInfluxDB(url);
            String sqlf = "SELECT SUM(avglength) as avglength,MAX(maxlength) AS maxlength FROM %s where %s and time>='%s' and time<'%s' group by zoneid,time(%s) fill(0) order by time  tz('%s')";
            String sql = String.format(sqlf, table, zoneIdOr, startTime, endTime, groupBy, timeZone);
            log.debug("------ [{}] ", sql);
            QueryResult queryResult = influxDB.query(new Query(sql, dbName, true));

            return queryResult2T(queryResult, QueueLength.class);
        } else {
            return Collections.emptyList();
        }
    }

    /**
     * 客流 只根据 time group by
     *
     * @param table
     * @param zoneIds
     * @param startTime
     * @param endTime
     * @param groupBy
     * @return
     */
    public List<Passengerflow> selectflowGroupByTime(String timeZone, TableName.Passengerflow table, List<Long> zoneIds, String startTime, String endTime, String groupBy) {

        String zoneIdOr = zoneIdOrStr(zoneIds);
        if (zoneIdOr != null) {
            InfluxDB influxDB = InfluxdbBaseDao.MyInfluxDBUtil.getInfluxDB(url);
            String sqlf = "SELECT SUM(countenter) as countEnter,SUM(countexit) as countExit FROM %s where %s and time>='%s' and time<'%s' group by time(%s) fill(none) order by time  tz('%s')";
            String sql = String.format(sqlf, table.value(), zoneIdOr, startTime, endTime, groupBy, timeZone);
            log.debug("------ [{}] ", sql);
            QueryResult queryResult = influxDB.query(new Query(sql, dbName, true));
            return queryResult2T(queryResult, Passengerflow.class);
        }
        return Collections.emptyList();
    }

    public List<PassByFlow> selectPassByFlowGrid(String timeZone, TableName.PassByflow table, List<Long> zoneIds, String startTime, String endTime) {
        QueryResult queryResult = selectGrid(timeZone, table.value(), zoneIds, startTime, endTime);
        if (queryResult != null) {
            return queryResult2T(queryResult, PassByFlow.class);
        } else {
            return Collections.emptyList();
        }
    }

    public List<DwellTime> selectDwellTimeGrid(String timeZone, TableName.Dwell table, List<Long> zoneIds, String startTime, String endTime) {
        QueryResult queryResult = selectGrid(timeZone, table.value(), zoneIds, startTime, endTime);
        if (queryResult != null) {
            return queryResult2T(queryResult, DwellTime.class);
        } else {
            return Collections.emptyList();
        }
    }

    public List<DwellTimePZ> selectDwellTimePZGrid(String timeZone, List<Long> zoneIds, String startTime, String endTime) {
        QueryResult queryResult = selectGrid(timeZone, TableName.Dwell.DwellTime_PZ.value(), zoneIds, startTime, endTime);
        if (queryResult != null) {
            return queryResult2T(queryResult, DwellTimePZ.class);
        } else {
            return Collections.emptyList();
        }
    }


    public List<EasAlarm> selectEasAlarmGrid(String timeZone, TableName.EasAlarm table, List<Long> zoneIds, String startTime, String endTime) {
        QueryResult queryResult = selectGrid(timeZone, table.value(), zoneIds, startTime, endTime);
        if (queryResult != null) {
            return queryResult2T(queryResult, EasAlarm.class);
        } else {
            return Collections.emptyList();
        }
    }

    public List<Passengerflow> selectFlowGrid(String timeZone, TableName.Passengerflow table, List<Long> zoneIds, String startTime, String endTime) {
        QueryResult queryResult = selectGrid(timeZone, table.value(), zoneIds, startTime, endTime);
        if (queryResult != null) {
            return queryResult2T(queryResult, Passengerflow.class);
        } else {
            return Collections.emptyList();
        }
    }

    private QueryResult selectGrid(String timeZone, String table, List<Long> zoneIds, String startTime, String endTime) {
        String zoneIdOr = zoneIdOrStr(zoneIds);
        if (zoneIdOr != null) {
            InfluxDB influxDB = InfluxdbBaseDao.MyInfluxDBUtil.getInfluxDB(url);
            String sqlf = "SELECT * FROM  %s  where %s and time>='%s' and time<'%s' order by time tz('%s')";
            String sql = String.format(sqlf, table, zoneIdOr, startTime, endTime, timeZone);
            log.debug("------ [{}] ", sql);
            QueryResult queryResult = influxDB.query(new Query(sql, dbName, true));
            return queryResult;
        }
        return null;
    }

    public List<EasAlarm> selectEasAlarmByZoneId(String timeZone, TableName.EasAlarm table, List<Long> zoneIds, String startTime, String endTime, String groupBy) {
        String zoneIdOr = zoneIdOrStr(zoneIds);
        if (zoneIdOr != null) {
            InfluxDB influxDB = InfluxdbBaseDao.MyInfluxDBUtil.getInfluxDB(url);
            String sqlf = "SELECT SUM(alarmtimes) as alarmTimes FROM %s where %s and time>='%s' and time<'%s' group by zoneid %s fill(0) order by time  tz('%s')";
            if (groupBy.equals("All")) {
                groupBy = "";
            } else {
                groupBy = String.format(", time(%s)", groupBy);
            }
            String sql = String.format(sqlf, table.value(), zoneIdOr, startTime, endTime, groupBy, timeZone);
            log.debug("------ [{}] ", sql);
            QueryResult queryResult = influxDB.query(new Query(sql, dbName, true));

            return queryResult2T(queryResult, EasAlarm.class);

        } else {
            return Collections.emptyList();
        }
    }

    /***
     * 根据 zoneId 和 Time 进行 group by
     * @param isFill 是否填充 fill(0)
     * @param timeZone
     * @param table
     * @param zoneIds
     * @param startTime
     * @param endTime
     * @param groupBy
     * @return
     */
    public List<Passengerflow> selectflowGroupByZoneId(boolean isFill, String timeZone, TableName.Passengerflow table, List<Long> zoneIds, String startTime, String endTime, String groupBy) {
        String zoneIdOr = zoneIdOrStr(zoneIds);
        if (zoneIdOr != null) {
            InfluxDB influxDB = InfluxdbBaseDao.MyInfluxDBUtil.getInfluxDB(url);
            StringBuilder sqlf = new StringBuilder("SELECT SUM(countenter) as countEnter,SUM(countexit) as countExit FROM %s where %s and time>='%s' and time<'%s' group by zoneid %s");
            if (isFill) {
                sqlf.append(" fill(0) ");
            }
            sqlf.append(" order by time tz('%s') ");
            if (groupBy.equals("All")) {
                groupBy = "";
            } else {
                groupBy = String.format(", time(%s)", groupBy);
            }
            String sql = String.format(sqlf.toString(), table.value(), zoneIdOr, startTime, endTime, groupBy, timeZone);
            log.debug("------ [{}] ", sql);
            QueryResult queryResult = influxDB.query(new Query(sql, dbName, true));

            return queryResult2T(queryResult, Passengerflow.class);
        } else {
            return Collections.emptyList();
        }
    }

    public List<EasAlarm> selectEasAlarmGroup_offset_ByZoneId(String timeZone, TableName.EasAlarm table, List<Long> zoneIds, String startTime, String endTime, String groupBy, String offset) {
        String zoneIdOr = zoneIdOrStr(zoneIds);
        if (zoneIdOr != null) {
            InfluxDB influxDB = InfluxdbBaseDao.MyInfluxDBUtil.getInfluxDB(url);
            String sqlf = "SELECT SUM(alarmTimes) as alarmTimes, FROM %s where %s and time>='%s' and time<'%s' group by zoneid , time(%s,%s) fill(0) order by time  tz('%s')";
            String sql = String.format(sqlf, table.value(), zoneIdOr, startTime, endTime, groupBy, offset, timeZone);
            log.debug("------ [{}] ", sql);
            QueryResult queryResult = influxDB.query(new Query(sql, dbName, true));

            return queryResult2T(queryResult, EasAlarm.class);

        } else {
            return Collections.emptyList();
        }
    }

    public List<Passengerflow> selectflowGroup_offset_By_zoneid_Time(String timeZone, TableName.Passengerflow table, List<Long> zoneIds, String startTime, String endTime, String groupBy, String offset) {
        String zoneIdOr = zoneIdOrStr(zoneIds);
        if (zoneIdOr != null) {
            InfluxDB influxDB = InfluxdbBaseDao.MyInfluxDBUtil.getInfluxDB(url);
            String sqlf = "SELECT SUM(countenter) as countEnter,SUM(countexit) as countExit FROM %s where %s and time>='%s' and time<'%s' " +
                    "group by zoneid,time(%s,%s) fill(0) order by time  tz('%s')";
            String sql = String.format(sqlf, table.value(), zoneIdOr, startTime, endTime, groupBy, offset, timeZone);
            log.debug("------ [{}] ", sql);
            QueryResult queryResult = influxDB.query(new Query(sql, dbName, true));
            return queryResult2T(queryResult, Passengerflow.class);
        } else {
            return Collections.emptyList();
        }
    }

    public List<PassByFlow> selectPassByFlowGroup_offset_By_zoneid_Time(String timeZone, TableName.PassByflow table, List<Long> zoneIds, String startTime, String endTime, String groupBy, String offset) {
        String zoneIdOr = zoneIdOrStr(zoneIds);
        if (zoneIdOr != null) {
            InfluxDB influxDB = InfluxdbBaseDao.MyInfluxDBUtil.getInfluxDB(url);
            String sqlf = "SELECT SUM(countenter) as countEnter,SUM(countpass) as countPass FROM %s where %s and time>='%s' and time<'%s' " +
                    "group by zoneid,time(%s,%s) fill(0) order by time  tz('%s')";
            String sql = String.format(sqlf, table.value(), zoneIdOr, startTime, endTime, groupBy, offset, timeZone);
            log.debug("------ [{}] ", sql);
            QueryResult queryResult = influxDB.query(new Query(sql, dbName, true));
            return queryResult2T(queryResult, PassByFlow.class);
        }
        return new ArrayList<>();
    }

    public List<PassByFlow> selectPassByFlowGroupByZoneId(String timeZone, TableName.PassByflow table, List<Long> zoneIds, String startTime, String endTime, String groupBy) {
        String zoneIdOr = zoneIdOrStr(zoneIds);
        if (zoneIdOr != null) {
            InfluxDB influxDB = InfluxdbBaseDao.MyInfluxDBUtil.getInfluxDB(url);
            String sqlf = "SELECT SUM(countenter) as countEnter,SUM(countpass) as countPass FROM %s where %s and time>='%s' and time<'%s' group by zoneid %s fill(0) order by time  tz('%s')";
            if (groupBy.equals("All")) {
                groupBy = "";
            } else {
                groupBy = String.format(", time(%s)", groupBy);
            }
            String sql = String.format(sqlf, table.value(), zoneIdOr, startTime, endTime, groupBy, timeZone);
            log.debug("------ [{}] ", sql);
            QueryResult queryResult = influxDB.query(new Query(sql, dbName, true));

            return queryResult2T(queryResult, PassByFlow.class);
        } else {
            return Collections.emptyList();
        }
    }

    public List<DwellTime> selectStayGroupByZoneId(String timeZone, String table, String zoneId, String startTime, String endTime, String groupBy) {
        InfluxDB influxDB = InfluxdbBaseDao.MyInfluxDBUtil.getInfluxDB(url);
        String sqlf = "SELECT SUM(numberserved) as StayCount,SUM(secondsOccupied) as secondsCount,SUM(secondsOccupied)/SUM(numberserved) as StayDuration FROM %s where zoneid = '%s' and time>='%s' and time<'%s' group by zoneid,time(%s) fill(0) order by time  tz('%s')";
        String sql = String.format(sqlf, table, zoneId, startTime, endTime, groupBy, timeZone);
        QueryResult queryResult = influxDB.query(new Query(sql, dbName, true));

        return queryResult2T(queryResult, DwellTime.class);
    }

    /**
     * @param table
     * @param zoneId
     * @param startTime
     * @param endTime
     * @param groupBy
     * @return
     */
    public List<Passengerflow> selectflowGroupByZoneId(String timeZone, String table, String zoneId, String startTime, String endTime, String groupBy) {
        InfluxDB influxDB = InfluxdbBaseDao.MyInfluxDBUtil.getInfluxDB(url);
        String sqlf = "SELECT SUM(countenter) as countEnter,SUM(countexit) as countExit FROM %s where zoneid = '%s' and time>='%s' and time<'%s'  group by zoneid,time(%s) fill(none) order by time  tz('%s')";

        String sql = String.format(sqlf, table, zoneId, startTime, endTime, groupBy, timeZone);
        QueryResult queryResult = influxDB.query(new Query(sql, dbName, true));
        return queryResult2T(queryResult, Passengerflow.class);
    }

    public List<QueueLength> selectQueueLengthGroupByZoneId(String timeZone, String table, String zoneId, String startTime, String endTime, String groupBy) {
        InfluxDB influxDB = InfluxdbBaseDao.MyInfluxDBUtil.getInfluxDB(url);
        String sqlf = "SELECT time,SUM(avglength) AS avglength,MAX(maxlength) AS maxlength FROM %s where zoneid = '%s' and time >= '%s' AND time <'%s' group by zoneid,time(%s) fill(none) order by time  tz('%s')";
        String sql = String.format(sqlf, table, zoneId, startTime, endTime, groupBy, timeZone);
        QueryResult queryResult = influxDB.query(new Query(sql, dbName, true));
        return queryResult2T(queryResult, QueueLength.class);
    }

    /**
     * 分组查询客流数据
     *
     * @param table
     * @param startTime
     * @param endTime
     * @param groupBy
     * @return
     */
    public List<Passengerflow> selectflowGroupBy(String timeZone, String table, String startTime, String endTime, String groupBy) {
        InfluxDB influxDB = InfluxdbBaseDao.MyInfluxDBUtil.getInfluxDB(url);
        String sqlf = "SELECT SUM(countenter) as countEnter,SUM(countexit) as countExit FROM %s where time>='%s' and time<'%s'   group by zoneid,time(%s) fill(none) order by time  tz('%s')";

        String sql = String.format(sqlf, table, startTime, endTime, groupBy, timeZone);
        QueryResult queryResult = influxDB.query(new Query(sql, dbName, true));
        List<Passengerflow> result = queryResult2T(queryResult, Passengerflow.class);
        return result;
    }


    private String zoneIdOrStr(List<Long> zoneIds) {
        if (zoneIds != null && zoneIds.size() > 0) {
            StringBuilder zsb = new StringBuilder();
            for (int i = 0; i < zoneIds.size(); i++) {
                if (i == (zoneIds.size() - 1)) {
                    zsb.append(String.format("zoneid='%s'", zoneIds.get(i)));
                } else {
                    zsb.append(String.format("zoneid='%s' OR ", zoneIds.get(i)));
                }
            }
            return zsb.toString();
        } else {
            return null;
        }
    }

    private <T> List<T> queryResult2T(QueryResult queryResult, Class<T> tClass) {
        return queryResult2T(queryResult, tClass, "zoneid");
    }

    private <T> List<T> queryResult2T(QueryResult queryResult, Class<T> tClass, String tag) {
        if (!queryResult.hasError()) {
            if (queryResult.getResults().size() > 0) {
                List<QueryResult.Result> results = queryResult.getResults();
                if (results != null) {
                    for (QueryResult.Result result : results) {
                        List<QueryResult.Series> series = result.getSeries();
                        if (series != null) {
                            List<T> resultList = new ArrayList<>();
                            for (QueryResult.Series qs : series) {
                                String zoneid = null;
                                if (qs.getTags() != null) {
                                    zoneid = qs.getTags().get(tag);
                                }
                                List<String> columns = qs.getColumns();
                                List<List<Object>> values = qs.getValues();
                                for (List<Object> v : values) {
                                    JSONObject object = new JSONObject();
                                    if (zoneid != null) {
                                        object.put(tag, zoneid);
                                    }
                                    for (int i = 0; i < columns.size(); i++) {
                                        object.put(columns.get(i), v.get(i));
                                    }
                                    resultList.add(JSON.parseObject(object.toJSONString(), tClass));
                                }
                            }
                            return resultList;
                        } else {
                            log.debug("------ no data");
                        }
                    }
                }
            } else {
                log.debug("------ no data");
            }
        } else {
            log.error(queryResult.getError());
        }
        return new ArrayList<>();

    }


}
